const sqlite3 = require('sqlite3').verbose();
const path = require('path');
//导入配置模块
const config = require("../../common/config");



class DeviceCategoryDB {
  constructor() {
    this.dbFile = config.dbfile;
    this.instance = null;
    this.db = null;
  }

  async connect() {
    if (this.db) return; // 避免重复连接
    this.db = new sqlite3.Database(this.dbFile, (err) => {
      if (err) {
        console.error('打开数据库失败', err);
        throw err;
      }
    });
    // 确保表存在
    await this.run(`CREATE TABLE IF NOT EXISTS device_categories (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT UNIQUE NOT NULL,
      description TEXT,
      created_at DATETIME DEFAULT (datetime('now','localtime')),
      updated_at DATETIME DEFAULT (datetime('now','localtime'))
    )`);
  }

  run(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function (err) {
        if (err) reject(err);
        else resolve(this);
      });
    });
  }

  get(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.get(sql, params, (err, row) => {
        if (err) reject(err);
        else resolve(row);
      });
    });
  }

  all(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  }

  // 新增一条分类
  async add({ name, description }) {
    const sql = `INSERT INTO device_categories (name, description, created_at, updated_at) VALUES (?, ?, datetime('now','localtime'), datetime('now','localtime'))`;
    const result = await this.run(sql, [name, description]);
    return result.lastID;
  }

  // 按ID查询分类
  async find(id) {
    const sql = `SELECT * FROM device_categories WHERE id = ?`;
    return this.get(sql, [id]);
  }

  // 查询所有分类
  async findAll() {
    const sql = `SELECT * FROM device_categories ORDER BY id ASC`;
    return this.all(sql);
  }

  // 按名字查分类（用于检查重名）
  async findByName(name) {
    const sql = `SELECT * FROM device_categories WHERE name = ?`;
    return this.get(sql, [name]);
  }

  // 更新分类
  async update({ id, name, description }) {
    const sql = `UPDATE device_categories SET name = ?, description = ?, updated_at = datetime('now','localtime') WHERE id = ?`;
    const result = await this.run(sql, [name, description, id]);
    return result.changes;
  }

  // 删除分类
  async remove(id) {
    const sql = `DELETE FROM device_categories WHERE id = ?`;
    const result = await this.run(sql, [id]);
    return result.changes;
  }

  static getInstance() {
    if (!DeviceCategoryDB.instance) {
      DeviceCategoryDB.instance = new DeviceCategoryDB();
    }
    return DeviceCategoryDB.instance;
  }
}

module.exports = DeviceCategoryDB;